﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using bdxyit.com.RecruitmentAppSys.IDAL;
using System.Data.SqlClient;
using bdxyit.com.RecruitmentAppSys.DBUtility;
using System.Data;
using bdxyit.com.RecruitmentAppSys.Models;

/***
*机器名：BDXYIT-HUI
*项目名称：$projectname$
*命名空间：bdxyit.com.RecruitmentAppSys.SQLDAL
*文件名：SkillListModel
*系统时间：3/25/2012 3:37:26 PM
*当前用户：huihui
*创建时间：2012
*
*修改时间：2012 3/25/2012 3:37:26 PM
*修改事件：
***/

namespace bdxyit.com.RecruitmentAppSys.SQLDAL
{
    public class SkillListDAL : ISkillListDAL
    {
        private const string SQL_SELECT_SKILLLIST = " select SkillID,SkillName from SkillLists ";
        private const string SQL_SELECT_SKILLLIST_BY_SkillID = " select  top 1 SkillID,SkillName from SkillLists "
                                                                + "  where SkillID=@SkillID";
        private const string SQL_DELETE_SKILLLIST_BY_SkillID = " delete from SkillLists "
                                                                + "  where SkillID=@SkillID";
        private const string SQL_UPDATE_SKILLLIST_BY_SkillID = " update SkillLists set "
                                                                + " SkillID=@SkillID,"
                                                                + " SkillName=@SkillName"
                                                                + "  where SkillID=@SkillID";
        private const string SQL_INSERT_SKILLLIST = " insert into SkillLists("
                                                    + " SkillID,SkillName)"
                                                    + "  values ("
                                                    + " @SkillID,@SkillName)";

        private const string FIELD_SkillID = "@SkillID";
        private const string FIELD_SkillName = "@SkillName";



        /// <summary>
        /// 删除一条记录根据skillid
        /// </summary>
        /// <param name="SkillID"></param>
        /// <returns></returns>
        public bool DeleteSkillListBySkillID(string SkillID)
        {
            bool flag = false;
            //创建参数
            SqlParameter parm = new SqlParameter(FIELD_SkillID, SqlDbType.VarChar, 10);
            parm.Value = SkillID;
            SqlCommand cmd = new SqlCommand();
            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocal))
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SQL_DELETE_SKILLLIST_BY_SkillID;
                cmd.Parameters.Add(parm);
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                flag = true;
            }
            return flag;
        }

        /// <summary>
        /// 插入一条记录
        /// </summary>
        /// <param name="sil"></param>
        /// <returns></returns>
        public bool InsertSkillList(SkillList sil)
        {
            bool flag = false;

            SqlParameter[] SkillListParms = GetSkillListParameters();
            SqlCommand cmd = new SqlCommand();
            SkillListParms[0].Value = sil.SkillID;
            SkillListParms[1].Value = sil.SkillName;

            foreach (SqlParameter parm in SkillListParms)
                cmd.Parameters.Add(parm);

            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocal))
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SQL_INSERT_SKILLLIST;
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                flag = true;
            }
            return flag;
        }

        /// <summary>
        ///更新数据根据SkillID
        /// </summary>
        /// <param name="sil"></param>
        /// <returns></returns>
        public bool UpdateSkillList(SkillList sil)
        {
            bool flag = false;
            SqlParameter[] SkillListParms = GetSkillListParameters();
            SqlCommand cmd = new SqlCommand();
            SkillListParms[0].Value = sil.SkillID;
            SkillListParms[1].Value = sil.SkillName;


            foreach (SqlParameter parm in SkillListParms)
                cmd.Parameters.Add(parm);

            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocal))
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SQL_UPDATE_SKILLLIST_BY_SkillID;
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                flag = true;
            }
            return flag;
        }

        /// <summary>
        /// 根据SkillID查询一条记录
        /// </summary>
        /// <param name="SkillID"></param>
        /// <returns></returns>
        public SkillList SelectSkillListBySkillID(string SkillID)
        {
            SkillList skillList = null;
            //创建参数
            SqlParameter parm = new SqlParameter(FIELD_SkillID, SqlDbType.VarChar, 10);
            parm.Value = SkillID;

            //执行读取家庭成员
            using (SqlDataReader rdr = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocal, CommandType.Text, SQL_SELECT_SKILLLIST_BY_SkillID, parm))
            {
                if (rdr.Read())
                {
                    skillList = new SkillList();
                    if (rdr.HasRows == true)
                    {
                        if (rdr["SkillID"] != null && rdr["SkillID"].ToString() != "")
                        {
                            skillList.SkillID = rdr["SkillID"].ToString();
                        }
                        if (rdr["SkillName"] != null && rdr["SkillName"].ToString() != "")
                        {
                            skillList.SkillName = rdr["SkillName"].ToString();
                        }
                    }
                }
            }
            return skillList;
        }

        /// <summary>
        /// 查询所有数据
        /// </summary>
        /// <returns></returns>
        public List<SkillList> SelectSkillList()
        {
            List<SkillList> L_skl = new List<SkillList>();
            using (SqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocal, CommandType.Text, SQL_SELECT_SKILLLIST))
            {
                while (dr.Read())
                {
                    SkillList skl = new SkillList();
                    skl.SkillID = dr["SkillID"].ToString();
                    skl.SkillName = dr["SkillName"].ToString();
                    L_skl.Add(skl);
                }
            }
            return L_skl;
        }


        private SqlParameter[] GetSkillListParameters()
        {
            SqlParameter[] parms = new SqlParameter[] {
                    new SqlParameter(FIELD_SkillID, SqlDbType.VarChar,10),
					new SqlParameter(FIELD_SkillName, SqlDbType.VarChar,50)};
            return parms;
        }

    }
}